'''
Case Type： 故障&可靠性测试
Case Name： 主节点索引文件丢失
Create At: 2020/07/20
@zou_jialiang0505328114
Descption:
1.数据库建表及索引，并插入数据；
2.手动删除主机的索引文件未删除备机的索引文件；
3.重启数据库
4.查询相关执行计划；
5.索引文件
6.查询相关执行计划
'''
import os
import unittest
import _thread
import time
from yat.test import macro
from yat.test import Node
from testcase.utils.CommonSH import *
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Deletaduit(unittest.TestCase):
    commonshpri = CommonSH('PrimaryDbUser')
    commonshsta1 = CommonSH('Standby1DbUser')
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')
    common = Common('PrimaryRoot')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info('------------Opengauss_Reliability_Dbsys_Case195.py start------------')
        self.dbPrimaryDbUser = Node(node='PrimaryDbUser')
        self.dbPrimaryRoot = Node(node='PrimaryRoot')
        self.dbStandby1Root = Node(node='Standby1Root')
        self.dbStandby1DbUser = Node(node='Standby1DbUser')
        self.tblname = 'reliability_dbsys195'
        self.index = 'reliability_dbsys195_index'
        self.indexpath = ''
        self.Constant = Constant()

    def test_startdb(self):
        self.log.info('-------------------创建数据表，并插入数据-----------------------------------')
        result = self.common.createLargeNumTable(self.dbPrimaryDbUser, self.tblname, 1, 100000)
        self.log.info(result)
        self.assertIn(self.Constant.CREATE_TABLE_SUCCESS, result)
        self.assertIn('INSERT 0 100000', result)

        self.log.info('------------------- 创建索引-----------------------------------')
        result = self.commonshpri.executDbSql(f'create index {self.index} on {self.tblname}(t_id);')
        self.log.info(result)
        self.assertIn(self.Constant.CREATE_INDEX_SUCCESS_MSG, result)

        self.log.info('------------------- 判断查询是否使用索引-----------------------------------')
        result = self.commonshpri.executDbSql(f'explain select * from {self.tblname} where t_id=999;')
        self.log.info(result)
        self.assertIn(f'Scan on {self.index}', result)

        self.log.info('----------------------------查询索引文件路径----------------------')
        o_id = self.commonshpri.executDbSql(f'select oid from pg_class where relname=\'{self.index}\';')
        self.log.info(o_id)
        oid = o_id.splitlines()[-2]
        self.log.info(oid)
        result = self.commonshpri.executDbSql(f'Select pg_relation_filepath({oid}::regclass);')
        self.log.info(result)
        indexpath_tmp = result.splitlines()[-2].strip()
        self.indexpath = os.path.join(macro.DB_INSTANCE_PATH,indexpath_tmp)
        self.log.info(f'index path is: {self.indexpath}')

        self.log.info('----------------删除主节点索引文件----------------------')
        mvcmd = f'cp {self.indexpath} {self.indexpath}.testbak; rm -rf {self.indexpath}'
        self.log.info(mvcmd)
        self.dbPrimaryDbUser.sh(mvcmd)
        result = self.dbPrimaryDbUser.sh(f'ls -l {self.indexpath}*').result()
        self.log.info(result)

        self.log.info('-------------------重启数据库-------------------------------')
        result = self.commonshpri.stopDbCluster()
        self.assertTrue(result)
        result = self.commonshpri.startDbCluster()
        self.assertTrue(result)

        self.log.info('------------------- 判断查询是否使用索引-----------------------------------')
        result = self.commonshpri.executDbSql(f'explain select * from {self.tblname} where t_id=999;')
        self.log.info(result)
        self.assertIn(self.Constant.OPEN_FAIL, result)
        result = self.commonshpri.executDbSql(f'select * from {self.tblname} where t_id=999;')
        self.log.info(result)
        self.assertIn(self.Constant.OPEN_FAIL, result)
        result = self.commonshsta1.executDbSql(f'explain select * from {self.tblname} where t_id=999;')
        self.log.info(result)
        self.assertIn(f'Scan on {self.index}', result)
        result = self.commonshsta1.executDbSql(f'select * from {self.tblname} where t_id=999;')
        self.log.info(result)
        self.assertIn('1 row', result)

        self.log.info('----------------恢复主节点索引文件----------------------')
        mvcmd = f'mv {self.indexpath}.testbak {self.indexpath}'
        self.log.info(mvcmd)
        self.dbPrimaryDbUser.sh(mvcmd)

        self.log.info('------------------- 判断查询是否使用索引-----------------------------------')
        result = self.commonshpri.executDbSql(f'explain select * from {self.tblname} where t_id=999;')
        self.log.info(result)
        self.assertIn(f'Scan on {self.index}', result)
        result = self.commonshpri.executDbSql(f'select * from {self.tblname} where t_id=999;')
        self.log.info(result)
        self.assertIn('1 row', result)

    def tearDown(self):
        self.log.info('----------------------this is tearDown-----------------------')
        self.log.info('----------------恢复备节点索引文件----------------------')
        if self.indexpath != '':
            mvcmd = f'mv {self.indexpath}.testbak {self.indexpath}'
            self.log.info(mvcmd)
            self.dbPrimaryDbUser.sh(mvcmd)

        self.log.info('----------------------drop table-----------------------')
        result = self.commonshpri.executDbSql(f'drop table if exists {self.tblname} ;')
        self.log.info(result)

        self.log.info('------------------------------------reliability_dbsys195.py finish------------------------------------')
